<?php
/**
 * Created by PhpStorm.
 * User: luxuewen_gz
 * Date: 2018/8/6
 * Time: 19:13
 * //
 * //                            _ooOoo_
 * //                           o8888888o
 * //                           88" . "88
 * //                           (| -_- |)
 * //                           O\  =  /O
 * //                        ____/`---'\____
 * //                      .'  \\|     |//  `.
 * //                     /  \\|||  :  |||//  \
 * //                    /  _||||| -:- |||||-  \
 * //                    |   | \\\  -  /// |   |
 * //                    | \_|  ''\---/''  |   |
 * //                    \  .-\__  `-`  ___/-. /
 * //                  ___`. .'  /--.--\  `. . __
 * //               ."" '<  `.___\_<|>_/___.'  >'"".
 * //              | | :  `- \`.;`\ _ /`;.`/ - ` : | |
 * //              \  \ `-.   \_ __\ /__ _/   .-` /  /
 * //         ======`-.____`-.___\_____/___.-`____.-'======
 * //                            `=---='
 * //        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 * //                      佛祖保佑       永无BUG
 */

namespace app\index\controller;


use think\Controller;
use think\Db;
use think\Loader;

/**
 * Class Excel PHPExcel的导入与导出
 * @package app\index\controller
 */
class Excel extends Controller
{
    public function index(){
        if($this->request->isPost()){
            Loader::import('PHPExcel.PHPExcel', EXTEND_PATH);//方法二
            Loader::import('PHPExcel.PHPExcel.IOFactory.PHPExcel_IOFactory');
            Loader::import('PHPExcel.PHPExcel.Reader.Excel5');
            $objPHPExcel = new \PHPExcel();

            //获取表单上传文件
            $file = request()->file('excel');
            $info = $file->validate(['size'=>1567800,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');
            if($info){
                $exclePath = $info->getSaveName();  //获取文件名
                $file_name = ROOT_PATH . 'public' . DS . 'excel'. DS . $exclePath;   //上传文件的地址
                $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
                $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8
                echo "<pre>";
                $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
                array_shift($excel_array);  //删除第一个数组(标题);
                $data = [];
                $i=0;
                foreach($excel_array as $k=>$v) {
                    $data[$k]['name'] = $v[0];
                    $data[$k]['work'] = $v[1];
                    $data[$k]['age'] = $v[2];
                    $data[$k]['mobile'] = $v[3];
                    $data[$k]['phone'] = $v[4];
                    $data[$k]['te'] = $v[5];
                    $i++;
                }
                $success=Db::name('user')->insertAll($data); //批量插入数据
                //$i=
                $error=$i-$success;
                echo "总{$i}条，成功{$success}条，失败{$error}条。";
                // Db::name('t_station')->insertAll($city); //批量插入数据
            }else{
                // 上传失败获取错误信息
                echo $file->getError();
            }

        }
        return $this->fetch();
    }
    public function export(){
        Loader::import('PHPExcel.PHPExcel', EXTEND_PATH);//方法二
        Loader::import('PHPExcel.PHPExcel.IOFactory.PHPExcel_IOFactory');
        Loader::import('PHPExcel.PHPExcel.Reader.Excel5');

        $userName=Db::name('user')->select();
        $PHPExcel = new \PHPExcel();//实例化
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle("用户手机"); //给当前活动sheet设置名称
        $PHPSheet->setCellValue("A1","名字")->setCellValue("B1","年龄")->setCellValue("C1","工作")->setCellValue("D1","手机")->setCellValue("E1","电话")->setCellValue("F1","TE");//表格数据
        //$PHPSheet->setCellValue("A2","001")->setCellValue("B2","元宝");//表格数据
        $i=2;
        foreach($userName as $key=>$val){
            $PHPSheet->setCellValue('A'.$i,$val['name'])->setCellValue('B'.$i,$val['age'])->setCellValue('C'.$i,$val['work'])->setCellValue('D'.$i,$val['mobile'])->setCellValue('E'.$i,$val['phone'])->setCellValue('F'.$i,$val['te']);//表格数据
            $i++;
        }
        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");//创建生成的格式
        header('Content-Disposition: attachment;filename="用户信息.xlsx"');//下载下来的表格名
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
        exit;
    }

}